package com.sundaying.gooway.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.sundaying.gooway.model.UrlClass;
import com.sundaying.gooway.model.UrlItem;
import com.sundaying.gooway.util.DbTool;

public class UrlClassService {
	/**
	 * 获取所有网址类别集合对象
	 * 
	 * @return List 网址类别集合对象
	 */
	public List<UrlClass> list() {
		List<UrlClass> urlClassList = new ArrayList();

		Connection conn = DbTool.createConn();
		String sql = "select * from GOW_URLCLASS";
		PreparedStatement ps = DbTool.prepare(conn, sql);
		try {
			ResultSet rs = ps.executeQuery();
			UrlClass urlClass = null;
			while (rs.next()) {
				urlClass = new UrlClass();
				urlClass.setClassID(rs.getInt("CLASSID"));
				urlClass.setClassName(rs.getString("CLASSNAME"));
				urlClass.setParentID(rs.getInt("PARENTID"));
				urlClassList.add(urlClass);
			}
			System.out.println("查询返回记录数：" + urlClassList.size());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DbTool.close(ps);
		DbTool.close(conn);

		return urlClassList;
	}

	/**
	 * 获取所有网址类别集合对象
	 * 
	 * @return List 网址类别集合对象
	 */
	public List<UrlClass> list(int classID) {
		List<UrlClass> urlClassList = new ArrayList();

		Connection conn = DbTool.createConn();
		String sql = "select * from GOW_URLCLASS where PARENTID=?";
		PreparedStatement ps = DbTool.prepare(conn, sql);
		try {
			ps.setInt(1, classID);
			ResultSet rs = ps.executeQuery();
			UrlClass urlClass = null;
			while (rs.next()) {
				urlClass = new UrlClass();
				urlClass.setClassID(rs.getInt("CLASSID"));
				urlClass.setClassName(rs.getString("CLASSNAME"));
				urlClass.setParentID(rs.getInt("PARENTID"));
				urlClassList.add(urlClass);
			}
			System.out.println("查询返回记录数：" + urlClassList.size());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DbTool.close(ps);
		DbTool.close(conn);

		return urlClassList;
	}

	/**
	 * 获取所有网址类别集合对象，使用Tree形展示
	 * 
	 * @return List 网址类别集合对象
	 */
	public List<Map<String, Object>> tree(int classID) {
		List<UrlClass> nodes = null;
		nodes = this.list(classID);

		List<Map<String, Object>> urlClassList = new ArrayList();
		for (UrlClass urlClass : nodes) {
			Map<String, Object> urlClassItem = new HashMap<String, Object>();
			urlClassItem.put("id", urlClass.getClassID());
			urlClassItem.put("text", urlClass.getClassName());

			if (this.hasChild(urlClass.getClassID())) {
				urlClassItem.put("state", "closed");
				urlClassItem.put("children", this.tree(urlClass.getClassID()));
			}

			urlClassList.add(urlClassItem);
		}

		return urlClassList;
	}

	/*
	 * 当前节点是否有子节点
	 */
	private boolean hasChild(int classID) {
		boolean result = false;

		Connection conn = DbTool.createConn();
		String sql = "select count(*) count from GOW_URLCLASS where PARENTID=?";
		PreparedStatement ps = DbTool.prepare(conn, sql);
		try {
			ps.setInt(1, classID);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int count = rs.getInt("count");
				if (count > 0)
					result = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DbTool.close(ps);
		DbTool.close(conn);

		return result;
	}

	/**
	 * 添加网址类别
	 * 
	 * @param UrlClass
	 *            网址类别对象
	 * @return boolean 是否添加成功
	 */
	public boolean add(UrlClass urlClass) {
		// 分类的上级分类不能是自己
		if (urlClass.getClassID() == urlClass.getParentID()) {
			urlClass.setParentID(0);
		}

		try {
			Connection conn = DbTool.createConn();
			String sql = "insert into GOW_URLCLASS(CLASSID, CLASSNAME, PARENTID) values(?, ?, ?)";
			PreparedStatement ps = DbTool.prepare(conn, sql);
			try {
				ps.setInt(1, urlClass.getClassID());
				ps.setString(2, urlClass.getClassName());
				ps.setInt(3, urlClass.getParentID());
				ps.executeUpdate();
				System.out.println("添加网址分类成功：" + urlClass.getClassName());
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DbTool.close(ps);
			DbTool.close(conn);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}

		return false;
	}

	/**
	 * 获取某个网址对象
	 * 
	 * @param String
	 *            网址的ID
	 * @return UrlItem 网址对象
	 */
	public UrlClass loadByID(String classID) {
		UrlClass urlClass = null;
		try {
			Connection conn = DbTool.createConn();
			String sql = "select * from GOW_URLCLASS where CLASSID = ?";
			PreparedStatement ps = DbTool.prepare(conn, sql);
			try {
				ps.setString(1, classID);
				ResultSet rs = ps.executeQuery();

				if (rs.next()) {
					urlClass = new UrlClass();
					urlClass.setClassID(rs.getInt("CLASSID"));
					urlClass.setClassName(rs.getString("CLASSNAME"));
					urlClass.setParentID(rs.getInt("PARENTID"));
				}
				System.out.println("查询返回类别：" + urlClass.getClassName());
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DbTool.close(ps);
			DbTool.close(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return urlClass;
	}

	/**
	 * 更新某个网址类别
	 * 
	 * @param UrlClass
	 *            网址类别
	 * @return boolean 是否更新成功
	 */
	public boolean update(UrlClass urlClass) {
		// 分类的上级分类不能是自己
		if (urlClass.getClassID() == urlClass.getParentID()) {
			urlClass.setParentID(0);
		}
		
		try {
			Connection conn = DbTool.createConn();
			String sql = "update GOW_URLCLASS set CLASSNAME=?, PARENTID=? where CLASSID=?";
			PreparedStatement ps = DbTool.prepare(conn, sql);
			try {
				ps.setString(1, urlClass.getClassName());
				ps.setInt(2, urlClass.getParentID());
				ps.setInt(3, urlClass.getClassID());
				ps.executeUpdate();
				System.out.println("已更新网址ID：" + urlClass.getClassID());
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DbTool.close(ps);
			DbTool.close(conn);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean delete(String classID) {
		try {
			Connection conn = DbTool.createConn();
			String sql = "delete from GOW_URLCLASS where CLASSID=?";
			PreparedStatement ps = DbTool.prepare(conn, sql);
			try {
				ps.setString(1, classID);
				ps.executeUpdate();
				System.out.println("已删除网址ID：" + classID);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DbTool.close(ps);
			DbTool.close(conn);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
}
